CREATE TABLE [dbo].[tb_ConceptosDelAgentePorOrganizacion]
(
[IdConceptoDelAgentePorOrganizacion] [int] NOT NULL IDENTITY(1, 1),
[Agente] [Agente] NOT NULL,
[Organizacion] [Organizacion] NOT NULL,
[Concepto] [int] NOT NULL,
[FechaDeCarga] [datetime] NOT NULL CONSTRAINT [DF_tb_ConceptosDelAgentePorOrganizacion_FechaDeCarga] DEFAULT (getdate()),
[ValidezHasta] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUserAutor] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tb_ConceptosDelAgentePorOrganizacion_NTUserAutor] DEFAULT (suser_sname())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[mantenervalidezhasta] ON [dbo].[tb_ConceptosDelAgentePorOrganizacion]
instead of  INSERT 
AS
 
declare @id INT
declare @idencontrado int
declare @agente INT
declare @organizacion int
declare @concepto int
declare @fechadecarga DATETIME
declare @validezhasta varchar(10)
DECLARE @ntuser varchar(30)
declare @esprivada varchar(1)
set @idencontrado=null


select @id=IdConceptoDelAgentePorOrganizacion,
	   @agente=Agente,
	   @organizacion=Organizacion,
	   @concepto=Concepto,
	   @fechadecarga=FechaDeCarga,
	   @validezhasta=ValidezHasta,
	   @ntuser=NTUserAutor
FROM inserted 

select @esprivada=esprivada from tb_organizaciones where idorganizaciones=@organizacion


		SELECT top 1 @idencontrado= IdConceptoDelAgentePorOrganizacion FROM tb_ConceptosDelAgentePorOrganizacion
		         WHERE  (Agente=@agente AND organizacion =@organizacion AND Concepto =@concepto AND ValidezHasta IS NULL)


if (@concepto=12 or @concepto=33 or @concepto=36 or @concepto=44 ) and @esprivada='S'  
	begin

 		if @idencontrado is not null and @validezhasta is not null 
                                 BEGIN
                         		UPDATE tb_ConceptosDelAgentePorOrganizacion  SET ValidezHasta =@validezhasta WHERE IdConceptoDelAgentePorOrganizacion =@idencontrado
                                        set @idencontrado=null 
		      END	
                     
                          if @idencontrado is null 
			INSERT INTO dbo.tb_ConceptosDelAgentePorOrganizacion
				(
				
				Agente,
				Organizacion,
				Concepto,
				FechaDeCarga,
				ValidezHasta,
				NTUserAutor
				)
			VALUES 
				(
				
				@agente,
				@organizacion,
				@concepto,
				@fechadecarga,
				null,
				@ntuser
				)
		

end



GO
ALTER TABLE [dbo].[tb_ConceptosDelAgentePorOrganizacion] WITH NOCHECK ADD CONSTRAINT [FK_tb_ConceptosDelAgentePorOrganizacion_tb_Agentes] FOREIGN KEY ([Agente]) REFERENCES [dbo].[tb_Agentes] ([idAgente]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_ConceptosDelAgentePorOrganizacion] WITH NOCHECK ADD CONSTRAINT [FK_tb_ConceptosDelAgentePorOrganizacion_tb_ConceptosDeLiquidacion] FOREIGN KEY ([Concepto]) REFERENCES [dbo].[tb_ConceptosDeLiquidacion] ([idConceptoLiquidacion]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_ConceptosDelAgentePorOrganizacion] WITH NOCHECK ADD CONSTRAINT [FK_tb_ConceptosDelAgentePorOrganizacion_tb_Organizaciones] FOREIGN KEY ([Organizacion]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones]) NOT FOR REPLICATION
GO
